package com.iplant.aps.serviceimpl.dao.sfc;

import com.iplant.aps.service.mesenum.*;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.sfc.SFCQualityLossBPMItem;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;

public class SFCQualityLossBPMItemDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(SFCQualityLossBPMItemDAO.class);

    private static SFCQualityLossBPMItemDAO Instance = null;

    private SFCQualityLossBPMItemDAO() {
        super();
    }

    public static SFCQualityLossBPMItemDAO getInstance() {
        if (Instance == null)
            Instance = new SFCQualityLossBPMItemDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, SFCQualityLossBPMItem wSFCQualityLossBPMItem,
                      OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wSFCQualityLossBPMItem == null)
                return 0;

            String wSQL = "";
            if (wSFCQualityLossBPMItem.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.sfc_qualitylossbpmitem(SFCQualityLossBPMID,BOMID," +
                                "BOMItemID,MaterialID,MaterialNumber,UnitID,Level,Responsibility,SapType," +
                                "ReviewComments,Status,Remark,OperatorID,Operator,OperateTime,Quota,QualityLossBig," +
                                "QualityLossSmall,ShiftID,ReplaceType,OutsourceType,UnitPrice,TotalPrice) VALUES(:SFCQualityLossBPMID," +
                                ":BOMID,:BOMItemID,:MaterialID,:MaterialNumber,:UnitID,:Level,:Responsibility," +
                                ":SapType,:ReviewComments,:Status,:Remark,:OperatorID,:Operator,:OperateTime,:Quota," +
                                ":QualityLossBig,:QualityLossSmall,:ShiftID,:ReplaceType,:OutsourceType,:UnitPrice,:TotalPrice);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.sfc_qualitylossbpmitem SET SFCQualityLossBPMID = " +
                                ":SFCQualityLossBPMID,BOMID = :BOMID,BOMItemID = :BOMItemID,MaterialID = :MaterialID," +
                                "MaterialNumber = :MaterialNumber,UnitID = :UnitID,Level = :Level,Responsibility = " +
                                ":Responsibility,SapType = :SapType,ReviewComments = :ReviewComments,Status = " +
                                ":Status,Remark = :Remark,OperatorID = :OperatorID,Operator = :Operator,OperateTime =" +
                                " :OperateTime,Quota = :Quota,QualityLossBig = :QualityLossBig,QualityLossSmall = " +
                                ":QualityLossSmall,ShiftID = :ShiftID,ReplaceType = :ReplaceType,OutsourceType = " +
                                ":OutsourceType,UnitPrice=:UnitPrice,TotalPrice=:TotalPrice WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wSFCQualityLossBPMItem.ID);
            wParamMap.put("SFCQualityLossBPMID", wSFCQualityLossBPMItem.SFCQualityLossBPMID);
            wParamMap.put("BOMID", wSFCQualityLossBPMItem.BOMID);
            wParamMap.put("BOMItemID", wSFCQualityLossBPMItem.BOMItemID);
            wParamMap.put("MaterialID", wSFCQualityLossBPMItem.MaterialID);
            wParamMap.put("MaterialNumber", wSFCQualityLossBPMItem.MaterialNumber);
            wParamMap.put("UnitID", wSFCQualityLossBPMItem.UnitID);
            wParamMap.put("Level", wSFCQualityLossBPMItem.Level);
            wParamMap.put("Responsibility", wSFCQualityLossBPMItem.Responsibility);
            wParamMap.put("SapType", wSFCQualityLossBPMItem.SapType);
            wParamMap.put("ReviewComments", wSFCQualityLossBPMItem.ReviewComments);
            wParamMap.put("Status", wSFCQualityLossBPMItem.Status);
            wParamMap.put("Remark", wSFCQualityLossBPMItem.Remark);
            wParamMap.put("OperatorID", wSFCQualityLossBPMItem.OperatorID);
            wParamMap.put("Operator", wSFCQualityLossBPMItem.Operator);
            wParamMap.put("OperateTime", wSFCQualityLossBPMItem.OperateTime);
            wParamMap.put("Quota", wSFCQualityLossBPMItem.Quota);
            wParamMap.put("QualityLossBig", wSFCQualityLossBPMItem.QualityLossBig);
            wParamMap.put("QualityLossSmall", wSFCQualityLossBPMItem.QualityLossSmall);
            wParamMap.put("ShiftID", wSFCQualityLossBPMItem.ShiftID);
            wParamMap.put("ReplaceType", wSFCQualityLossBPMItem.ReplaceType);
            wParamMap.put("OutsourceType", wSFCQualityLossBPMItem.OutsourceType);
            wParamMap.put("UnitPrice", wSFCQualityLossBPMItem.UnitPrice);
            wParamMap.put("TotalPrice", wSFCQualityLossBPMItem.TotalPrice);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wSFCQualityLossBPMItem.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wSFCQualityLossBPMItem.setID(wResult);
            } else {
                wResult = wSFCQualityLossBPMItem.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<SFCQualityLossBPMItem> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (SFCQualityLossBPMItem wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.sfc_qualitylossbpmitem WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public SFCQualityLossBPMItem SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        SFCQualityLossBPMItem wResult = new SFCQualityLossBPMItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<SFCQualityLossBPMItem> wList = SelectList(wLoginUser, wID, -1, -1, -1, "", wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<SFCQualityLossBPMItem> SelectList(BMSEmployee wLoginUser, int wID, int wSFCQualityLossBPMID,
                                                  int wLevel, int wStatus, String wShiftID,
                                                  OutResult<Integer> wErrorCode) {
        List<SFCQualityLossBPMItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.MaterialNo,t1.MaterialName,t2.Name UnitText FROM {0}.sfc_qualitylossbpmitem t "
                            + " left join {0}.mss_material t1 on t.MaterialID=t1.ID "
                            + " left join {0}.cfg_unit t2 on t.UnitID=t2.ID "
                            +"WHERE  1=1  " +
                            "and ( :wID <= 0 or :wID = t.ID ) " +
                            "and ( :wSFCQualityLossBPMID <= 0 or :wSFCQualityLossBPMID = t.SFCQualityLossBPMID ) " +
                            "and ( :wLevel <= 0 or :wLevel = t.Level ) " +
                            "and ( :wStatus <= 0 or :wStatus = t.Status ) " +
                            "and ( :wShiftID is null or :wShiftID = '''' or :wShiftID = t.ShiftID );",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wSFCQualityLossBPMID", wSFCQualityLossBPMID);
            wParamMap.put("wLevel", wLevel);
            wParamMap.put("wStatus", wStatus);
            wParamMap.put("wShiftID", wShiftID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                SFCQualityLossBPMItem wItem = new SFCQualityLossBPMItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.SFCQualityLossBPMID = StringUtils.parseInt(wReader.get("SFCQualityLossBPMID"));
                wItem.BOMID = StringUtils.parseInt(wReader.get("BOMID"));
                wItem.BOMItemID = StringUtils.parseInt(wReader.get("BOMItemID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialNumber = StringUtils.parseDouble(wReader.get("MaterialNumber"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.UnitText = StringUtils.parseString(wReader.get("UnitText"));
                wItem.Level = StringUtils.parseInt(wReader.get("Level"));
                wItem.LevelName= SFCBOMTaskLevel.getEnumType(wItem.Level).getLable();
                wItem.Responsibility = StringUtils.parseInt(wReader.get("Responsibility"));
                wItem.ResponsibilityName= SFCBOMTaskResponsibility.getEnumType(wItem.Responsibility).getLable();
                wItem.SapType = StringUtils.parseInt(wReader.get("SapType"));
                wItem.SapTypeName= SFCBOMTaskSAPType.getEnumType(wItem.SapType).getLable();
                wItem.ReviewComments = StringUtils.parseInt(wReader.get("ReviewComments"));
                wItem.ReviewCommentsName=SFCBOMTaskReviewComments.getEnumType(wItem.ReviewComments).getLable();
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.OperatorID = StringUtils.parseInt(wReader.get("OperatorID"));
                wItem.Operator = StringUtils.parseString(wReader.get("Operator"));
                wItem.OperateTime = StringUtils.parseCalendar(wReader.get("OperateTime"));
                wItem.Quota = StringUtils.parseDouble(wReader.get("Quota"));
                wItem.QualityLossBig = StringUtils.parseString(wReader.get("QualityLossBig"));
                wItem.QualityLossSmall = StringUtils.parseString(wReader.get("QualityLossSmall"));
                wItem.QualityLossBigName= SFCQualityLossBigType.getEnumType(StringUtils.parseInt(wItem.QualityLossBig)).getLable();
                wItem.QualityLossSmallName=SFCQualityLossSmallType.getEnumType(StringUtils.parseInt(wItem.QualityLossSmall)).getLable();
                wItem.ShiftID = StringUtils.parseString(wReader.get("ShiftID"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceTypeText=SFCReplaceType.getEnumType(wItem.ReplaceType).getLable();
                wItem.OutsourceType = StringUtils.parseInt(wReader.get("OutsourceType"));
                wItem.OutsourceTypeText=SFCOutSourceType.getEnumType(wItem.OutsourceType).getLable();
                wItem.UnitPrice = StringUtils.parseDouble(wReader.get("UnitPrice"));
                wItem.TotalPrice = StringUtils.parseDouble(wReader.get("TotalPrice"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     *根据物料关键字模糊查询，主单据任务ID集合
     */
    public List<Integer> SelectTaskIDListByMaterial(BMSEmployee wLoginUser, String wMaterial, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if(StringUtils.isEmpty(wMaterial)){
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT distinct t1.SFCQualityLossBPMID FROM {0}.sfc_qualitylossbpmitem t1,{0}.mss_material " +
                            "t2 where t1.MaterialID=t2.ID and (t2.MaterialName like ''%{1}%'' or t2.MaterialNo like ''%{1}%'');",
                    wInstance.Result,wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wSFCQualityLossBPMID = StringUtils.parseInt(wReader.get("SFCQualityLossBPMID"));
                wResult.add(wSFCQualityLossBPMID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
